2022 iThome 鐵人賽

DAY 24

Software Development

ClickHouse:時序資料庫建置與運行系列第 24 篇

day24-ClickHouse 客戶端連線介面方式(三)

14th鐵人賽

peter279k

2022-10-08 00:23:24

1216 瀏覽

分享至

前言

在本章節中，我們將要介紹的是ClickHouse官方提供的JDBC與ODBC連線函式庫。

JDBC Driver介紹

ClickHouse官方有提供了JDBC的連線方式使用，專案位置如下：
https://github.com/ClickHouse/clickhouse-jdbc

上述專案可以用來讓Java的應用程式可以存取ClickHouse資料庫。

當然還有一些第三方的JDBC連線套件，相關的第三方連線套件如下：

ClickHouse-Native-JDBC, https://github.com/housepower/ClickHouse-Native-JDBC
clickhouse4j, https://github.com/blynkkk/clickhouse4j

下方的範例我們會以Linux發行版本之Ubuntu 18.04上建置Java環境並搭配官方提供的ClickHouse JDBC連線套件進行示範。

一開始我們先建置Java環境，下列是安裝Java環境的方法與執行指令所輸出的訊息：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get update
Get:1 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]
Hit:2 http://mirrors.digitalocean.com/ubuntu bionic-updates InRelease
Hit:3 http://mirrors.digitalocean.com/ubuntu bionic-backports InRelease
Hit:4 https://packages.clickhouse.com/deb stable InRelease
Hit:5 http://deb.anydesk.com all InRelease
Hit:6 http://security.ubuntu.com/ubuntu bionic-security InRelease
Fetched 242 kB in 1s (428 kB/s)
Reading package lists... Done
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install git
[sudo] password for peter:
Reading package lists... Done
Building dependency tree
Reading state information... Done
git is already the newest version (1:2.17.1-1ubuntu0.12).
git set to manually installed.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install default-jdk
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  ca-certificates-java default-jdk-headless default-jre default-jre-headless fonts-dejavu-extra
  java-common libatk-wrapper-java libatk-wrapper-java-jni libgif7 libice-dev libpthread-stubs0-dev
  libsm-dev libx11-dev libx11-doc libxau-dev libxcb1-dev libxdmcp-dev libxt-dev openjdk-11-jdk
  openjdk-11-jdk-headless openjdk-11-jre openjdk-11-jre-headless x11proto-core-dev x11proto-dev
  xorg-sgml-doctools xtrans-dev
......
Unpacking default-jre (2:1.11-68ubuntu1~18.04.1) ...
Selecting previously unselected package openjdk-11-jdk-headless:amd64.
Preparing to unpack .../07-openjdk-11-jdk-headless_11.0.16+8-0ubuntu1~18.04_amd64.deb ...
Unpacking openjdk-11-jdk-headless:amd64 (11.0.16+8-0ubuntu1~18.04) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ java -version
openjdk version "11.0.16" 2022-07-19
OpenJDK Runtime Environment (build 11.0.16+8-post-Ubuntu-0ubuntu118.04)
OpenJDK 64-Bit Server VM (build 11.0.16+8-post-Ubuntu-0ubuntu118.04, mixed mode, sharing)
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install maven
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
......
Setting up libmaven-shared-utils-java (3.3.0-1~18.04) ...
Setting up libsisu-inject-java (0.3.2-2) ...
Setting up libsisu-plexus-java (0.3.3-3) ...
Setting up libmaven3-core-java (3.6.0-1~18.04.1) ...
Setting up maven (3.6.0-1~18.04.1) ...
update-alternatives: using /usr/share/maven/bin/mvn to provide /usr/bin/mvn (mvn) in auto mode
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ mvn -version
Apache Maven 3.6.0
Maven home: /usr/share/maven
Java version: 11.0.16, vendor: Ubuntu, runtime: /usr/lib/jvm/java-11-openjdk-amd64
Default locale: en, platform encoding: UTF-8
OS name: "linux", version: "4.15.0-193-generic", arch: "amd64", family: "unix"

從上述的執行指令與輸出的訊息可以知道，我們先安裝了Java的JDK，版本為11；接著再安裝Maven，作為Java相依使用的套件管理工具。需要的Java環境都安裝完成之後，使用git指令將範例的專案庫複製回來，相關執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ git clone https://github.com/peter279k/hello-clickhouse
Cloning into 'hello-clickhouse'...
remote: Enumerating objects: 12, done.
remote: Counting objects: 100% (12/12), done.
remote: Compressing objects: 100% (7/7), done.
remote: Total 12 (delta 0), reused 12 (delta 0), pack-reused 0
Unpacking objects: 100% (12/12), done.
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cd hello-clickhouse/
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ mvn compile
......
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  1.221 s
[INFO] Finished at: 2022-10-04T07:31:38Z
[INFO] ------------------------------------------------------------------------
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ mvn package
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.inject.internal.cglib.core.$ReflectUtils$1 (file:/usr/share/maven/lib/guice.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of com.google.inject.internal.cglib.core.$ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
[INFO] Scanning for projects...
[INFO]
[INFO] ---------------< org.hello-clickhouse:hello-clickhouse >----------------
[INFO] Building hello-clickhouse 0.1.0
......
[INFO] Dependency-reduced POM written at: /home/peter/hello-clickhouse/dependency-reduced-pom.xml
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  3.935 s
[INFO] Finished at: 2022-10-04T07:32:52Z
[INFO] ------------------------------------------------------------------------
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ java -jar target/hello-clickhouse-0.1.0.jar
blogs
default
git_clickhouse
mgbench
system

從上述執行的指令與輸出的訊息可以知道，先將專案複製到當前的機器之後，接著再切換該專案目錄後，執行mvn compile進行專案的相依安裝與編譯Java檔變成byte-code，之後再執行mvn package指令將byte-code的檔案封裝成JAR檔案。

若要修改函式庫的版本，可以參考pom.xml的第21行進行修改，現在範例目前設定的版本為：0.3.2-patch11。

最後執行封裝的JAR檔案後，就成功的運行與建置簡易的使用JDBC連線到ClickHouse資料庫的範例了，我們可以使用文字編輯器打開src/main/java/hello/HelloDB.java檔案，這邊以vim編輯器為例子，相關的執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ vim src/main/java/hello/HelloDB.java
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ cat src/main/java/hello/HelloDB.java
package hello;

import java.sql.*;
import java.util.*;
import com.clickhouse.jdbc.*;

public class HelloDB {
        public static void main(String[] args) {
            String url = "jdbc:ch:https://play.clickhouse.com:443";
            Properties properties = new Properties();
            properties.setProperty("user", "explorer");
            properties.setProperty("password", "");
            properties.setProperty("client_name", "Agent #1");

            try {
                ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("show databases");
                while (rs.next()) {
                    System.out.println(rs.getString("name"));
                }
            } catch (SQLException e) {
                 e.printStackTrace();
                 System.out.println("Cannot connect the ClickHouse DB server");
            }
        }
}
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$

若要連到我們自己的ClickHouse資料庫，則可以修改src/main/java/hello/HelloDB.java之Java程式碼，修改的地方包含了url變數、使用者名稱與密碼，而client_name的設定為可選的，也可以不用加入這一行，相關修改後的程式碼如下：

package hello;

import java.sql.*;
import java.util.*;
import com.clickhouse.jdbc.*;

public class HelloDB {
        public static void main(String[] args) {
            String url = "jdbc:ch:http://127.0.0.1:8123";
            Properties properties = new Properties();
            properties.setProperty("user", "default");
            properties.setProperty("password", "password");
            properties.setProperty("client_name", "Agent #1");

            try {
                ClickHouseDataSource dataSource = new ClickHouseDataSource(url, properties);
                Connection conn = dataSource.getConnection();
                Statement stmt = conn.createStatement();
                ResultSet rs = stmt.executeQuery("show databases");
                while (rs.next()) {
                    System.out.println(rs.getString("name"));
                }
            } catch (SQLException e) {
                 e.printStackTrace();
                 System.out.println("Cannot connect the ClickHouse DB server");
            }
        }
}

修改完程式碼之後，接著再分別執行mvn compile與mvn package指令進行編譯與封裝新的JAR檔，接著再執行該封裝好的JAR檔，相關的執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ mvn compile
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.inject.internal.cglib.core.$ReflectUtils$1 (file:/usr/share/maven/lib/guice.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of com.google.inject.internal.cglib.core.$ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
WARNING: All illegal access operations will be denied in a future release
......
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  1.338 s
[INFO] Finished at: 2022-10-04T08:18:08Z
[INFO] ------------------------------------------------------------------------
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ mvn package
WARNING: An illegal reflective access operation has occurred
WARNING: Illegal reflective access by com.google.inject.internal.cglib.core.$ReflectUtils$1 (file:/usr/share/maven/lib/guice.jar) to method java.lang.ClassLoader.defineClass(java.lang.String,byte[],int,int,java.security.ProtectionDomain)
WARNING: Please consider reporting this to the maintainers of com.google.inject.internal.cglib.core.$ReflectUtils$1
WARNING: Use --illegal-access=warn to enable warnings of further illegal reflective access operations
......
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time:  3.941 s
[INFO] Finished at: 2022-10-04T08:18:16Z
[INFO] ------------------------------------------------------------------------
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/hello-clickhouse$ java -jar target/hello-clickhouse-0.1.0.jar
HELLOWORLD
INFORMATION_SCHEMA
db_comment
default
information_schema
system
test

以上就是連線到我們自己的ClickHouse資料庫伺服器的範例，同時要注意幾件事情：

版本需要Java 8以上才可以運行上述相關的程式。
JDBC或是Java客戶端函式庫（clickhouse-client）的函式庫從0.3.2版本開始，僅支援ClickHouse 20.7版本以上。

ODBC Driver介紹

我們可以使用ClickHouse官方提供的ODBC來當作資料來源以存取資料庫，主要有兩種ODBC的驅動可以選擇：

UnixODBC - 此為在類Unix的作業系統中最常見的ODBC驅動，這也可以運行在Windows作業系統並搭配Cygwin或是MSYS/MinGW環境中。
iODBC - 此為較不常見的ODBC驅動，主要也是用在類Unix的作業系統中。這也是在macOS作業系統用的ODBC驅動，這也可以運行在Windows作業系統並搭配Cygwin或是MSYS/MinGW環境中。

我們以Ubuntu 18.04當作發行版本為例，相關需要安裝套件執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get update
Get:1 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]
Hit:2 http://mirrors.digitalocean.com/ubuntu bionic-updates InRelease
Hit:3 http://mirrors.digitalocean.com/ubuntu bionic-backports InRelease
Hit:4 https://packages.clickhouse.com/deb stable InRelease
Get:5 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]
Hit:6 http://deb.anydesk.com all InRelease
Fetched 331 kB in 1s (327 kB/s)
Reading package lists... Done
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install openssl libicu-dev unixodbc
Reading package lists... Done
Building dependency tree
Reading state information... Done
openssl is already the newest version (1.1.1-1ubuntu2.1~18.04.20).
openssl set to manually installed.
The following additional packages will be installed:
......
Setting up autotools-dev (20180224.1) ...
Setting up libtool (2.4.6-2) ...
Setting up unixodbc-dev:amd64 (2.3.4-1.1ubuntu3) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

從上述的輸出訊息可以知道，我們以UnixODBC為例進行相依、建置與編譯，相關需要安裝的套件指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ sudo apt-get install build-essential git libpoco-dev libssl-dev libicu-dev unixodbc-dev
Reading package lists... Done
.......
After this operation, 59.0 MB of additional disk space will be used.
Do you want to continue? [Y/n]
......
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
Processing triggers for libc-bin (2.27-3ubuntu1.6) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ wget -O - https://apt.kitware.com/keys/kitware-archive-latest.asc 2>/dev/null | gpg --dearmor - | sudo tee /etc/apt/trusted.gpg.d/kitware.gpg >/dev/null
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ sudo apt-add-repository "deb https://apt.kitware.com/ubuntu/ $(lsb_release -cs) main"
Get:1 http://mirrors.digitalocean.com/ubuntu bionic InRelease [242 kB]
Hit:2 http://mirrors.digitalocean.com/ubuntu bionic-updates InRelease
Hit:3 http://mirrors.digitalocean.com/ubuntu bionic-backports InRelease
Hit:4 http://deb.anydesk.com all InRelease
Hit:5 http://security.ubuntu.com/ubuntu bionic-security InRelease
Hit:6 https://packages.clickhouse.com/deb stable InRelease
Get:7 https://apt.kitware.com/ubuntu bionic InRelease [11.0 kB]
Get:8 https://apt.kitware.com/ubuntu bionic/main amd64 Packages [60.9 kB]
Fetched 314 kB in 2s (154 kB/s)
Reading package lists... Done
......
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ sudo apt-get install cmake
......
Setting up cmake-data (3.24.1-0kitware1ubuntu18.04.1) ...
Setting up cmake (3.24.1-0kitware1ubuntu18.04.1) ...
Processing triggers for man-db (2.8.3-2ubuntu0.1) ...
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$

從上面輸出的訊息要注意的是，因為cmake指令在Ubnutu 18.04的作業系統發行版本中為舊版，而ClickHouse之ODBC驅動需要更新的版本，因此需要匯入kitware儲存庫來安裝新版的cmake。

相關建置的過程與執行的指令所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ git clone --recursive https://github.com/ClickHouse/clickhouse-odbc.git
Cloning into 'clickhouse-odbc'...
remote: Enumerating objects: 6785, done.
remote: Counting objects: 100% (510/510), done.
remote: Compressing objects: 100% (243/243), done.
remote: Total 6785 (delta 262), reused 422 (delta 228), pack-reused 6275
Receiving objects: 100% (6785/6785), 5.26 MiB | 10.64 MiB/s, done.
.......
Submodule path 'contrib/poco/gradle': checked out '6ea2234083fd4710c33da06f5791583f8da4bfe4'
Submodule path 'contrib/poco/openssl': checked out '0c6d16ec85f80ea3ce05a8f2ff52c1b3ba240a41'
Submodule path 'contrib/ssl': checked out '17c23a0296a19288136c2fee9077dc9423b79bc7'
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cd clickhouse-odbc/
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc$ mkdir build
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc$ cd build/
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ cmake -DCMAKE_BUILD_TYPE=RelWithDebInfo ..
-- The C compiler identification is GNU 7.5.0
-- The CXX compiler identification is GNU 7.5.0
.......
-- Build files have been written to: /home/peter/clickhouse-odbc/build
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ cmake --build . --config RelWithDebInfo
[  0%] Building CXX object contrib/poco/Foundation/CMakeFiles/Foundation.dir/src/ASCIIEncoding.cpp.o
......
[ 91%] Built target NetSSL
[ 91%] Building CXX object driver/CMakeFiles/clickhouse-odbc-impl.dir/utils/type_parser.cpp.o
[ 91%] Building CXX object driver/CMakeFiles/clickhouse-odbc-impl.dir/utils/type_info.cpp.o
[ 91%] Building CXX object driver/CMakeFiles/clickhouse-odbc-impl.dir/utils/unicode_converter.cpp.o
[ 91%] Building CXX object driver/CMakeFiles/clickhouse-odbc-impl.dir/utils/conversion_context.cpp.o
[ 91%] Building CXX object driver/CMakeFiles/clickhouse-odbc-impl.dir/config/config.cpp.o
......
[100%] Building CXX object driver/test/CMakeFiles/clickhouse-odbcw-nano-it.dir/nano_it.cpp.o
[100%] Linking CXX executable clickhouse-odbcw-nano-it
[100%] Built target clickhouse-odbcw-nano-it
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$

編譯完成之後，我們以Python為例並使用pyodbc進行連線，相關的指令執行與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ sudo apt-get install python3-pip
[sudo] password for peter:
Reading package lists... Done
Building dependency tree
Reading state information... Done
......
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ pip3 install -U pyodbc
Collecting pyodbc
Installing collected packages: pyodbc
.....
Successfully installed pyodbc-4.0.34

接著我們要編輯/etc/odbcinst.ini與/etc/odbc.ini設定檔，我們以vim文字編輯器為例，相關的設定檔編輯與執行指令如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ sudo vim /etc/odbcinst.ini
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ cat /etc/odbcinst.ini
[ODBC Drivers]
ClickHouse ODBC Driver (ANSI)    = Installed
ClickHouse ODBC Driver (Unicode) = Installed

[ClickHouse ODBC Driver (ANSI)]
Description = ODBC Driver (ANSI) for ClickHouse
Driver      = /home/peter/clickhouse-odbc/build/driver/libclickhouseodbc.so
Setup       = /home/peter/clickhouse-odbc/build/driver/libclickhouseodbc.so
UsageCount  = 1

[ClickHouse ODBC Driver (Unicode)]
Description = ODBC Driver (Unicode) for ClickHouse
Driver      = /home/peter/clickhouse-odbc/build/driver/libclickhouseodbcw.so
Setup       = /home/peter/clickhouse-odbc/build/driver/libclickhouseodbcw.so
UsageCount  = 1
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ sudo vim /etc/odbc.ini
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~/clickhouse-odbc/build$ cat /etc/odbc.ini
[ODBC Data Sources]
ClickHouse DSN (ANSI)    = ClickHouse ODBC Driver (ANSI)
ClickHouse DSN (Unicode) = ClickHouse ODBC Driver (Unicode)

[ClickHouse DSN (ANSI)]
Driver      = ClickHouse ODBC Driver (ANSI)
Description = DSN (localhost) for ClickHouse ODBC Driver (ANSI)

Server = localhost
Database = default
UID = default
PWD = password
Port = 8123
Proto = http

[ClickHouse DSN (Unicode)]
Driver      = ClickHouse ODBC Driver (Unicode)
Description = DSN (localhost) for ClickHouse ODBC Driver (Unicode)

Server = localhost
Database = default
UID = default
PWD = password
Port = 8123
Proto = http

從上述的設定檔可以知道，我們分別設定了ODBC驅動連線資訊以及指定ODBC驅動使用的動態函式庫，更多這兩個的設定檔案方式可以從參考資料中章節的連結中找到。這邊我們用系統層級的方式去定義ODBC之連線資訊，當然我們也可以建立使用者層級的設定，若要建立使用者層級設定則是在當前使用者的家目錄中分別建立.odbcinst.ini與.odbc.ini之設定檔案。

在建立ODBC連線資訊時，即odbc.ini之設定檔案，使用的是舊的方法定義連線資訊，新的連線方法可以參考在參考資料章節中的連結。

從odbc.ini之設定可以知道，我們在設定檔中分別定義了ClickHouse DSN (ANSI)與ClickHouse DSN (Unicode)連線設定，設定完成後，可以編輯下列的Python程式碼，相關的執行指令與輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ vim connect.py
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ cat connect.py
import pyodbc


connect = pyodbc.connect('DSN=ClickHouse DSN (Unicode)')
cursor = connect.cursor()
cursor.tables()
rows = cursor.fetchall()

print(rows)

從上面編輯好的connect.py之程式碼可以知道，我們指定了ClickHouse DSN (Unicode)的連線資訊進行連線，當連線成功之後，取得資料庫底下的資料表並將資料表清單印出，執行上述的connect.py之Python程式碼所輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ python3 connect.py
[('default', None, 'describe_example', 'TABLE', None), ('default', None, 'example_back', 'TABLE', None), ('default', None, 'grpc_example_table', 'TABLE', None), ('default', None, 'insert_select_testtable', 'TABLE', None), ('default', None, 'limit_by', 'TABLE', None), ('default', None, 'simple_table', 'TABLE', None), ('default', None, 't1', 'TABLE', None), ('default', None, 't2', 'TABLE', None), ('default', None, 'table_from_file', 'TABLE', None), ('default', None, 'table_with_comment', 'TABLE', None), ('default', None, 'table_with_ttl', 'TABLE', None), ('default', None, 'temp', 'TABLE', None), ('default', None, 'ttt', 'TABLE', None)]

若我們要執行SQL語句，則可以使用cursor.execute的方法來達成，相關的Python程式碼可以改成如下：

import pyodbc


connect = pyodbc.connect('DSN=ClickHouse DSN (Unicode)')
cursor = connect.cursor()
cursor.execute('SELECT version()')
rows = cursor.fetchall()

print(rows)

執行上述的Python程式碼之後，相關輸出的訊息如下：

peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$ python3 connect.py
[('22.8.4.7', )]
peter@ubuntu-s-4vcpu-8gb-amd-sgp1-01:~$

結論

從這章節中，我們知道如何使用ClickHouse官方所提供的JDBC與ODBC等連線方式進行與資料庫的連線，在下一章節中，將會介紹ClickHouse官方的C++資料庫連線客戶端函式庫應用。

參考資料

day23-ClickHouse 客戶端連線介面方式(二)

day25-ClickHouse 客戶端連線介面方式(四)

系列文

ClickHouse:時序資料庫建置與運行共 30 篇

RSS系列文訂閱系列文

4 人訂閱

完整目錄

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

尚未有邦友留言

立即登入留言

參賽組數

1064 組

團體組數

40 組

累計文章數

22207 篇

完賽人數

600 人

15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js

IT邦幫忙

ClickHouse:時序資料庫建置與運行系列 第 24 篇